import numpy as np
import pandas as pd
import geopandas as gpd
import fiona
import glob
import os
import contextily as ctx
from scipy.spatial import cKDTree
from shapely.geometry import Point
import json
from tqdm.auto import tqdm
pd.set_option('min_rows', 30)
import sys
sys.path.append('..')
from importlib import reload
# import src.utils as utils
# reload(utils)
import matplotlib.pyplot as plt
plt.rcParams['figure.figsize'] = (12, 12)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 10)

%%time
## read in and get bounds
df = pd.read_csv("input/BCs_issued_by_AUP_TLADCs_2021FEB.csv")
bcs = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.X_Coordinate, df.Y_Coordinate))
# get bounds of data set (with a small buffer), useful for clipping other datasets
bcs = bcs.set_crs(epsg=4326)
bounds4326 = bcs.total_bounds
bcs = bcs.to_crs(epsg=3857)
bounds3857 =  bcs.total_bounds

CPU times: user 37.2 s, sys: 859 ms, total: 38 s
Wall time: 38 s

## add small buffer to bounds
x_delta3857 = bounds3857[2] - bounds3857[0]
y_delta3857 = bounds3857[3] - bounds3857[1]
bounds_buffered3857 = [bounds3857[0] - y_delta3857 / 200, bounds3857[1] - x_delta3857 / 200, bounds3857[2] + y_delta3857 / 200, bounds3857[3] + x_delta3857 / 200]
bounds_buffered3857
x_delta4326 = bounds4326[2] - bounds4326[0]
y_delta4326 = bounds4326[3] - bounds4326[1]
bounds_buffered4326 = [bounds4326[0] - y_delta4326 / 200, bounds4326[1] - x_delta4326 / 200, bounds4326[2] + y_delta4326 / 200, bounds4326[3] + x_delta4326 / 200]
bounds_buffered4326

[174.15865864485, -37.2937968355, 175.54295485515, -35.877583094500004]

# one address has no ADDRESS_1, but an ADDRESS_2 with a leading digit
display(bcs[bcs.ADDRESS_1.isna() & ~bcs.ADDRESS_2.isna()][bcs[bcs.ADDRESS_1.isna() & ~bcs.ADDRESS_2.isna()].ADDRESS_2.str.contains('^[0-9]', regex=True)])
# move address 2 to address 1 for that one BC...
bcs.loc[bcs.OBS == 146813, 'ADDRESS_1'] = "25 HOLLYFORD DRV"
bcs.loc[bcs.OBS == 146813, 'ADDRESS_2'] = ""

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry
64127	146813	Sep 01, 2013 12:00:00 AM	Mar 01, 2013 12:00:00 AM	NaN	25 HOLLYFORD DRV	CLOVER PARK	Howick	New (and pre-built) house, unit, bach, crib, b...	1100	239.0	240000.0	1	Residential	Detached	NaN	NaN	1998	716014.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.896997	-36.986932	POINT (19469444.625 -4437285.457)

%%time
# get number and name of street (but not 'road', 'street', 'place' etc), for matching with addresses
def number_name_bc(x):
    """extract street number and first complete word of the street name from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3]]).lower()
        return ' '.join(joined_address.split(' ')[:2])

def full_address_bc(x):
    """extract full address from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3] if not str(x[f'ADDRESS_{i}']) == 'nan']).lower()
        return joined_address

bcs['number_name'] = bcs.apply(number_name_bc, axis=1)
bcs['full_address'] = bcs.apply(full_address_bc, axis=1)
# to have successfully extracted the name as the second 'word': the last character is never numeric
# print(sum(bcs['number_name'].apply(lambda x: x[-1] in [i for i in range(10)])))
# there will still be some cases where there is no street number
display(bcs.number_name.sample(5))

22947        45 station
65398     22a stottholm
5621         19 ventura
149709         3 tainui
6642          111 kuaka
Name: number_name, dtype: object

CPU times: user 28.9 s, sys: 500 ms, total: 29.4 s
Wall time: 29.4 s

ax = bcs.sample(3000).plot(column='Building_Type_Group', legend=True, figsize=(20,20))
ctx.add_basemap(ax)

# split into three branches: those with an address number range (e.g. 10-20), those with a single number (e.g. 10), and those with no number
# those with no number cannot be matched with addresses to get a more accuract coordinate
bcs_non_na = bcs[~bcs.ADDRESS_1.isna()]
bcs_ranged = bcs_non_na[bcs_non_na.ADDRESS_1.str.contains('^[0-9]+-[0-9]', regex=True)]
# get all bcs which start with a digit
bcs_numbered = bcs_non_na[bcs_non_na.ADDRESS_1.str.contains('^[0-9]', regex=True)]
# exclude those in bcs_ranged
bcs_numbered = bcs_numbered[~bcs_numbered.ADDRESS_1.str.contains('^[0-9]+-[0-9]', regex=True)]
bcs_others = pd.concat([bcs_non_na[~bcs_non_na.ADDRESS_1.str.contains('^[0-9]', regex=True)], bcs[bcs.ADDRESS_1.isna()]])
print('non na:', len(bcs_non_na))
print('1. ranged:', len(bcs_ranged))
print('2. numbered:', len(bcs_numbered))
print('3. non ranged/non numbered:', len(bcs_others))
print('total bcs:', len(bcs))
print('sum of 1, 2, 3:', len(bcs_ranged) + len(bcs_numbered) + len(bcs_others))

non na: 193005
1. ranged: 6267
2. numbered: 183013
3. non ranged/non numbered: 17946
total bcs: 207226
sum of 1, 2, 3: 207226

# all ranges that have a non digit in them
rows = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
    r = row.number_name.split(' ')[0].split('/')[0]
    if not all([s.isdecimal() for s in r.split('-')]):
        print(row.number_name.split(' ')[0])
    rows.append(row)

1-1a
2-2a
9-9a
1-1a
3-5a
3-5a

Get more accurate coordinates from NZ addresses dataset

# addressing of building consents is very inconsistent
# some building consents have street name in ADDRESS_2, some have suburb, some even have 'Auckland'
bcs[bcs.ADDRESS_2 == 'Auckland'].sample(5)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry
24601	188214	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Non-building construction	3111	0.0	40000.0	1	Other	NaN	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)
24605	188218	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	129.0	233100.0	1	Residential	Attached	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)
24606	188219	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	104.0	188640.0	1	Residential	Attached	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)
24614	188227	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	524-526 Don Buck Road	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	135.0	216000.0	1	Residential	Attached	NaN	NaN	2014	226403.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_7	Living	Residential	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.607308	-36.824848	POINT (19437196.626 -4414720.873)
24618	188231	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	136.0	245622.0	1	Residential	Attached	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)

%%time
## read in address dataset and add number_name, just like for BCs
# https://data.linz.govt.nz/layer/53353-nz-street-address/
addresses = gpd.read_file('input/lds-nz-street-address-GPKG-CLIPPED.gpkg').to_crs(3857)
def number_name_addresses(x):
    return ' '.join(x.full_address.split(' ')[:2]).lower()
addresses['number_name'] = addresses.apply(number_name_addresses, axis=1)

/data/miniconda3/envs/house-upzone/lib/python3.8/site-packages/geopandas/geodataframe.py:577: RuntimeWarning: Sequential read of iterator was interrupted. Resetting iterator. This can negatively impact the performance.
  for feature in features_lst:

CPU times: user 2min 5s, sys: 11 s, total: 2min 16s
Wall time: 2min 16s

addresses[(addresses.address_number == 9) & (addresses.full_road_name == 'Hopetoun Street')].sample(3)

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name
513953	1989299	2991221	Road	202	9	None	NaN	None	None	Freemans Bay	Auckland	202/9	Hopetoun Street	202/9 Hopetoun Street, Freemans Bay, Auckland	189772	174.752374	-36.858544	None	None	Freemans Bay	Auckland	Hopetoun Street	202/9 Hopetoun Street, Freemans Bay, Auckland	POINT (19453345.297 -4419407.917)	202/9 hopetoun
513851	1915403	2991240	Road	502	9	None	NaN	None	None	Freemans Bay	Auckland	502/9	Hopetoun Street	502/9 Hopetoun Street, Freemans Bay, Auckland	189772	174.752374	-36.858544	None	None	Freemans Bay	Auckland	Hopetoun Street	502/9 Hopetoun Street, Freemans Bay, Auckland	POINT (19453345.297 -4419407.917)	502/9 hopetoun
513854	1915406	2991245	Road	603	9	None	NaN	None	None	Freemans Bay	Auckland	603/9	Hopetoun Street	603/9 Hopetoun Street, Freemans Bay, Auckland	189772	174.752374	-36.858544	None	None	Freemans Bay	Auckland	Hopetoun Street	603/9 Hopetoun Street, Freemans Bay, Auckland	POINT (19453345.297 -4419407.917)	603/9 hopetoun

addresses.sample(10)

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name
52975	1002801.0	889731.0	Road	None	2.0	None	NaN	None	None	Titirangi	Auckland	2	Waerenga Place	2 Waerenga Place, Titirangi, Auckland	171349.0	174.653251	-36.934016	None	None	Titirangi	Auckland	Waerenga Place	2 Waerenga Place, Titirangi, Auckland	POINT (19442311.009 -4429913.404)	2 waerenga
158403	998738.0	886362.0	Road	None	24.0	None	NaN	None	None	Te Atatu South	Auckland	24	Tiroroa Avenue	24 Tiroroa Avenue, Te Atatu South, Auckland	191180.0	174.648857	-36.869946	None	None	Te Atatu South	Auckland	Tiroroa Avenue	24 Tiroroa Avenue, Te Atatu South, Auckland	POINT (19441821.779 -4420994.377)	24 tiroroa
596494	2227003.0	5300930.0	Road	1702	10.0	None	NaN	None	None	Auckland Central	Auckland	1702/10	Commerce Street	1702/10 Commerce Street, Auckland Central, Auc...	164395.0	174.768026	-36.845663	None	None	Auckland Central	Auckland	Commerce Street	1702/10 Commerce Street, Auckland Central, Auc...	POINT (19455087.663 -4417615.980)	1702/10 commerce
318775	1921571.0	1745258.0	Road	None	7.0	B	NaN	None	None	St Heliers	Auckland	7B	Edison Place	7B Edison Place, St Heliers, Auckland	203321.0	174.843315	-36.867704	None	None	St Heliers	Auckland	Edison Place	7B Edison Place, St Heliers, Auckland	POINT (19463468.830 -4420682.420)	7b edison
270718	1147783.0	1013962.0	Road	None	4.0	None	NaN	None	None	Devonport	Auckland	4	Huia Street	4 Huia Street, Devonport, Auckland	192089.0	174.792322	-36.830337	None	None	Devonport	Auckland	Huia Street	4 Huia Street, Devonport, Auckland	POINT (19457792.320 -4415484.318)	4 huia
124870	1878596.0	1834835.0	Road	1	95.0	None	NaN	None	None	Papatoetoe	Auckland	1/95	Motatau Road	1/95 Motatau Road, Papatoetoe, Auckland	193672.0	174.863701	-36.961238	None	None	Papatoetoe	Auckland	Motatau Road	1/95 Motatau Road, Papatoetoe, Auckland	POINT (19465738.129 -4433705.285)	1/95 motatau
94373	1487614.0	1295132.0	Road	None	18.0	None	NaN	None	None	Riverhead	Riverhead	18	Maude Street	18 Maude Street, Riverhead	205312.0	174.593912	-36.756725	None	None	Riverhead	Riverhead	Maude Street	18 Maude Street, Riverhead	POINT (19435705.379 -4405251.420)	18 maude
202231	1498243.0	1304879.0	Road	None	87.0	None	NaN	None	None	Orewa	Orewa	87	Tauranga Place	87 Tauranga Place, Orewa	204608.0	174.674711	-36.589828	None	None	Orewa	Orewa	Tauranga Place	87 Tauranga Place, Orewa	POINT (19444699.877 -4382087.267)	87 tauranga
227912	1226642.0	1082889.0	Road	None	12.0	None	NaN	None	None	Clover Park	Auckland	12	Pesaro Place	12 Pesaro Place, Clover Park, Auckland	185178.0	174.888951	-36.978107	None	None	Clover Park	Auckland	Pesaro Place	12 Pesaro Place, Clover Park, Auckland	POINT (19468548.989 -4436055.601)	12 pesaro
434079	1489551.0	1296876.0	Road	None	11.0	None	NaN	None	None	Riverhead	Riverhead	11	Great North Road	11 Great North Road, Riverhead	167386.0	174.592630	-36.759447	None	None	Riverhead	Riverhead	Great North Road	11 Great North Road, Riverhead	POINT (19435562.668 -4405629.650)	11 great

# and alternative matching would have been to take the digits + the first n characters of each address up to but not including 'road', drive', 'avenue' etc... but that would have been messy
set([x.split(' ')[-1] for x in addresses.full_road_name])

{'1',
 '12',
 '16',
 '2',
 'Access',
 'Anchorage',
 'Ara-Kotinga',
 'Avenue',
 'Bay',
 'Boardwalk',
 'Boulevard',
 'Brae',
 'Broadway',
 'Bypass',
 'Carrowmore',
 'Central',
 'Circle',
 'Circus',
 'Close',
 'Common',
 'Concourse',
 'Court',
 'Courtneys',
 'Cove',
 'Crescent',
 'Crest',
 'Dale',
 'Dell',
 'Downs',
 'Drive',
 'Earlsway',
 'East',
 'Enclave',
 'End',
 'Esplanade',
 'Extension',
 'Fairway',
 'Fen',
 'Furlong',
 'Garden',
 'Gardens',
 'Glade',
 'Glebe',
 'Glen',
 'Goldfield',
 'Green',
 'Greens',
 'Grove',
 'Gully',
 'Harbour',
 'Haven',
 'Heights',
 'Highway',
 'Hill',
 'Hilltop',
 'Ho',
 'Homestead',
 'Island',
 'Isle',
 'Kingsway',
 'Knoll',
 'Landing',
 'Lane',
 'Lea',
 'Link',
 'Lochview',
 'Loop',
 'Mall',
 'Meadway',
 'Mermaid',
 'Mews',
 'Mile',
 'Motu',
 'Neptune',
 'Nook',
 'North',
 'Oaks',
 'Oasis',
 'Oho',
 'Oval',
 'Parade',
 'Park',
 'Parkway',
 'Pass',
 'Place',
 'Plaza',
 'Point',
 'Priors',
 'Prom',
 'Promenade',
 'Quadrant',
 'Queensway',
 'Reach',
 'Retreat',
 'Ridge',
 'Rise',
 'Ritz',
 'Road',
 'Rosebowl',
 'Row',
 'Serpentine',
 'Settlement',
 'Silverfield',
 'Slope',
 'South',
 'Spa',
 'Spinney',
 'Spur',
 'Square',
 'Strand',
 'Street',
 'Tahuhu',
 'Tai',
 'Terrace',
 'Track',
 'Trail',
 'Trees',
 'Treeway',
 'Vale',
 'Valley',
 'View',
 'Views',
 'Walk',
 'Watch',
 'Waterways',
 'Way',
 'West',
 'Willowbrook',
 "d'Amarres"}

%time
addresses_tree = cKDTree(np.array(list(addresses[~addresses.geometry.isna()].geometry.apply(lambda x: (x.x, x.y)))))
bcs_numbered_tree = cKDTree(np.array(list(bcs_numbered.geometry.apply(lambda x: (x.x, x.y)))))
bcs_ranged_tree = cKDTree(np.array(list(bcs_ranged.geometry.apply(lambda x: (x.x, x.y)))))

CPU times: user 5 µs, sys: 1 µs, total: 6 µs
Wall time: 10.3 µs

# test some radii to see which is suitable
bcs_numbered_sample = bcs_numbered.sample(1000)
bcs_numbered_sample_tree = cKDTree(np.array(list(bcs_numbered_sample.geometry.apply(lambda x: (x.x, x.y)))))
matches = {}
for r in [10, 50, 100, 175] + list(range(250, 3251, 250)):
    matches[r] = []
    # list of lists: ith sub list contains indices of use_tree points within r of the ith data_tree point
    bcs_numbered_neighbours = bcs_numbered_sample_tree.query_ball_tree(addresses_tree, r)
    for i, neighbours in tqdm(enumerate(bcs_numbered_neighbours)):
        # check how many matches there are
        matches[r].append(np.sum(bcs_numbered_sample.iloc[i].number_name == addresses.iloc[neighbours].number_name))

# for r in sorted(matches.keys()):
#     print('####', r, '####')
#     print('match:', np.sum(np.array(matches[r]) == 1) / len(matches[r]))
#     print('ambiguous:', np.sum(np.array(matches[r]) > 1) / len(matches[r]))
#     print('no match:', np.sum(np.array(matches[r]) == 0) / len(matches[r]))
r_list = list(sorted(matches.keys()))
ax = plt.subplot(1,1,1)
ax.plot(r_list, [np.sum(np.array(matches[r]) == 1) / len(matches[r]) for r in r_list], label='match')
ax.plot(r_list, [np.sum(np.array(matches[r]) > 1) / len(matches[r]) for r in r_list], label='ambiguous')
ax.plot(r_list, [np.sum(np.array(matches[r]) == 0) / len(matches[r]) for r in r_list], label='no match')
ax.legend()
plt.xlabel('radius')
plt.ylabel('proportion')
plt.grid()

# test some radii to see which is suitable
def unique_radius_column_match(left, right, left_col, right_col, r=1250, left_tree=None, right_tree=None):
    """find points in right that are within r of points in left and uniquely match on a column"""
    if right_tree is None:
        right_tree = cKDTree(np.array(list(right.geometry.apply(lambda x: (x.x, x.y)))))
    if left_tree is None:
        left_tree = cKDTree(np.array(list(left.geometry.apply(lambda x: (x.x, x.y)))))
    
    # specify columns and initialize neighbour information dictionary if using the less efficient method
    # columns = ('address_id', 'full_address', 'geometry')
    # neighbour_information = {c: [] for c in columns}
    match_indices = []
    # list of lists: ith sub list contains indices of right points within r of the ith left point
    left_tree_neighbours = left_tree.query_ball_tree(addresses_tree, r)
    for i, neighbours in tqdm(enumerate(left_tree_neighbours), total=len(left_tree_neighbours)):
        # subset right to those within r distance
        right_neighbours = addresses.iloc[neighbours]
        # check how many matches there are
        match_indicator = left.iloc[i][left_col] == right_neighbours[right_col]
        if np.sum(match_indicator) == 1:
            # extract the index from right_neighbours
            # extracting the index only and later subsetting the full addresses dataframe
            # results in a ~4x speed up over extracting the requisite addresses information within this for loop
            match_indices.append(right_neighbours[match_indicator].index[0])
        elif np.sum(match_indicator) > 1:
            match_indices.append(-1)
        else:
            match_indices.append(-2)
#     print('####', r, '####')
#     print('match:', np.sum(np.array(match_indices) > -1) / len(match_indices))
#     print('ambiguous:', np.sum(np.array(match_indices) == -1) / len(match_indices))
#     print('no match:', np.sum(np.array(match_indices) == -2) / len(match_indices))
    return match_indices

# add empty row with index -1; this will be retrieved if there is no match address match
addresses.loc[-1] = addresses.loc[1]
for c in addresses.columns:
    addresses.loc[-1, c] = np.nan
    addresses.loc[-1, c]
addresses.loc[-2] = addresses.loc[-1]

bcs_numbered_sample = bcs_numbered.sample(100)

addresses.loc[match_indices].geometry.reset_index(drop=True)

0    POINT (19445145.208 -4381779.063)
1    POINT (19431050.314 -4406402.836)
2    POINT (19451295.075 -4424310.520)
3    POINT (19472792.559 -4428320.864)
4                                 None
5    POINT (19451798.838 -4402948.354)
6    POINT (19448871.690 -4388815.649)
7                                 None
8    POINT (19441001.992 -4419707.478)
9    POINT (19471097.591 -4435539.799)
Name: geometry, dtype: geometry

r = 1250
match_col = 'number_name'
match_indices = unique_radius_column_match(bcs_numbered_sample, addresses, match_col, match_col, r=r, right_tree=addresses_tree)
bcs_numbered_sample['addresses_geometry'] = addresses.loc[match_indices].geometry.reset_index(drop=True).tolist()
bcs_numbered_sample['addresses_full_address'] = addresses.loc[match_indices].full_address.reset_index(drop=True).tolist()
bcs_numbered_sample['euc_distance'] = [np.sqrt((a[0] - b[0]) ** 2 + (a[1] - b[1]) ** 2) for a, b in zip(bcs_numbered_sample.geometry.apply(lambda x: (x.x, x.y)), bcs_numbered_sample['addresses_geometry'].apply(lambda x: (x.x, x.y) if not pd.isna(x) else (0, 0)))]

bcs_numbered_sample[~bcs_numbered_sample.addresses_full_address.isna()].sort_values('euc_distance').reset_index(drop=True).euc_distance.plot()
plt.xlabel('Building Consent')
plt.ylabel('Distance to Matched Address (metres)')

Text(0, 0.5, 'Distance to Matched Address (metres)')

with pd.option_context('display.max_rows', None, 'display.max_columns', None):
    display(bcs_numbered_sample.sort_values('euc_distance')[['full_address', 'addresses_full_address']])

	full_address	addresses_full_address
11910	302 buckland road mangere east auckland	302 Buckland Road, Mangere East, Auckland
14559	15f wonderview road leigh auckland	15F Wonderview Road, Leigh
23042	41 sunshine boulevard sunnyvale auckland	41 Sunshine Boulevard, Sunnyvale, Auckland
27621	5 cory road waiheke island auckland	5 Cory Road, Palm Beach, Waiheke Island
12352	22 aorangi place birkenhead auckland	22 Aorangi Place, Birkenhead, Auckland
27689	75 litten road cockle bay auckland	75 Litten Road, Cockle Bay, Auckland
1255	2 hakanoa street grey lynn auckland	2 Hakanoa Street, Grey Lynn, Auckland
7325	26 longfellow parade titirangi auckland	26 Longfellow Parade, Glen Eden, Auckland
5807	2 riverside drive waiuku auckland	2 Riverside Drive, Waiuku
4631	40 rimu road manurewa auckland	40 Rimu Road, Manurewa, Auckland
25134	8 paneika lane otahuhu auckland	8 Paneika Lane, Otahuhu, Auckland
1597	141 boundary road papakura auckland	141 Boundary Road, Papakura
6980	30 boocock crescent orewa auckland	30 Boocock Crescent, Orewa
25427	1 three oaks drive dairy flat auckland	1 Three Oaks Drive, Dairy Flat
22942	23 dinning road riverhead auckland	23 Dinning Road, Riverhead
26829	1 campbell road maraetai auckland	1 Campbell Road, Maraetai, Auckland
19203	88 godfrey drive silverdale auckland	88 Godfrey Drive, Orewa
89353	9 may ave te atatu south	9 May Avenue, Te Atatu South, Auckland
65938	17 arion rd takanini	17 Arion Road, Takanini
53063	20 whaley greens silverdale silverdale	20 Whaley Greens, Silverdale
179340	53 motutapu ave manly	53 Motutapu Avenue, Manly, Whangaparaoa
64497	13 wisteria lane silverdale	13 Wisteria Lane, Silverdale
147021	6 aldern rd massey	6 Aldern Road, Massey, Auckland
60541	2 conti dr flat bush	2 Conti Drive, Flat Bush, Auckland
78764	3 ruze vida dr massey	3 Ruze Vida Drive, Massey, Auckland
35010	8 bomber lane whenuapai auckland	8 Bomber Lane, Whenuapai, Auckland
149815	24 chateau rise east tamaki	24 Chateau Rise, Flat Bush, Auckland
54053	50 golden morning dr albany heights 0632	50 Golden Morning Drive, Albany Heights, Auckland
46805	52 pohutukawa parade riverhead	52 Pohutukawa Parade, Riverhead
46381	3 lagonda rise oteha	3 Lagonda Rise, Oteha, Auckland
42674	16 hakaro way takanini	16 Hakaro Way, Takanini
141546	13 bejoy rise howick	13 Bejoy Rise, East Tamaki Heights, Auckland
168598	18a newell st point chevalier	18A Newell Street, Point Chevalier, Auckland
176966	4 sonterra close manurewa	4 Sonterra Close, Randwick Park, Auckland
51613	18 hooton drive huapai huapai	18 Hooton Drive, Kumeu
165221	7 fintry pl flat bush	7 Fintry Place, Flat Bush, Auckland
67261	38 ashlynne ave papatoetoe	38 Ashlynne Avenue, Papatoetoe, Auckland
112620	108 picasso dr west harbour	108 Picasso Drive, West Harbour, Auckland
104439	105 duck creek rd stillwater	105 Duck Creek Road, Stillwater
180331	7 wharf st papakura	7 Wharf Street, Papakura
158528	90 kittiwake dr albany	90 Kittiwake Drive, Schnapper Rock, Auckland
159272	44 ploughmans ave pukekohe	44 Ploughmans Avenue, Pukekohe
35124	39 rautahi terrace redvale auckland	39 Rautahi Terrace, Redvale, Auckland
79595	43 james st mangere	43 James Street, Mangere East, Auckland
152618	50 tiriwa dr massey	50 Tiriwa Drive, Massey, Auckland
174996	3 springtide pl red beach	3 Springtide Place, Red Beach
149027	30 lynton rd mt wellington	30 Lynton Road, Mount Wellington, Auckland
194750	3 feeny cres east tamaki	3 Feeny Crescent, East Tamaki, Auckland
72140	43 landing dr albany	43 Landing Drive, Albany, Auckland
66497	96a harris street pukekohe	96A Harris Street, Pukekohe
203287	30 premila dr pukekohe	30 Premila Drive, Pukekohe
88891	8 pono pl waiuku	8 Pono Place, Waiuku
126494	4 argyle ave papakura	4 Argyle Avenue, Pahurehure, Papakura
125141	1 highwood grove torbay	1 Highwood Grove, Torbay, Auckland
29981	21 derrimore heights clover park auckland	21 Derrimore Heights, Clover Park, Auckland
163003	3 dellwood ave henderson	3 Dellwood Avenue, Henderson, Auckland
114145	5 rawson way takanini	5 Rawson Way, Takanini
94953	2/21 aorangi pl birkenhead	2/21 Aorangi Place, Birkenhead, Auckland
167225	89 mt lebanon cres manurewa	89 Mt Lebanon Crescent, The Gardens, Auckland
173149	7 drumbeg close flat bush	7 Drumbeg Close, Flat Bush, Auckland
78888	9 blanc rd silverdale	9 Blanc Road, Silverdale
106131	16 te hoe grove albany	16 Te Hoe Grove, Pinehill, Auckland
31267	91 karepiro drive stanmore bay auckland	91 Karepiro Drive, Stanmore Bay, Whangaparaoa
108386	95 atkin ave mission bay	95 Atkin Avenue, Mission Bay, Auckland
146677	26 duncansby rd stanmore bay	26 Duncansby Road, Stanmore Bay, Whangaparaoa
45224	2 gallantry crescent papakura	2 Gallantry Crescent, Papakura
154737	74 rangitoto rd papatoetoe	74 Rangitoto Road, Papatoetoe, Auckland
169823	39 hanene st st heliers	39 Hanene Street, St Heliers, Auckland
116371	57 picasso dr west harbour	57 Picasso Drive, West Harbour, Auckland
64541	50 corricvale way northcross	50 Corricvale Way, Northcross, Auckland
31281	2 valley road browns bay auckland	2 Valley Road, Browns Bay, Auckland
170825	1286 harvard lane ardmore	1286 Harvard Lane, Ardmore
185465	83 rathmar dr manurewa	83 Rathmar Drive, Manurewa, Auckland
101749	50 mountain rd mangere bridge	50 Mountain Road, Mangere Bridge, Auckland
194723	42 lady ruby dr east tamaki	42 Lady Ruby Drive, East Tamaki, Auckland
97647	8 robley cres glendowie	8 Robley Crescent, Glendowie, Auckland
123801	8 victory rd laingholm	8 Victory Road, Laingholm, Auckland
142017	74 coxhead rd manurewa	74 Coxhead Road, Manurewa, Auckland
102538	114 allum st kohimarama	114 Allum Street, Kohimarama, Auckland
179111	13a halver rd manurewa	13A Halver Road, Hillpark, Auckland
173036	70 walters rd papakura	70 Walters Road, Takanini
166356	12 mccallum dr sandspit	12 McCallum Drive, Sandspit
86995	584 blockhouse bay rd blockhouse bay	584 Blockhouse Bay Road, Blockhouse Bay, Auckland
36587	131 martyn wright road pukekohe auckland	131 Martyn Wright Road, Mauku
179618	5 georgina st freemans bay	5 Georgina Street, Freemans Bay, Auckland
138664	275 ormiston rd flat bush	275 Ormiston Road, Flat Bush, Auckland
48660	444b peach hill road drury 0	444B Peach Hill Road, Hunua
141993	77 gearon rd waiuku	77 Gearon Road, Mauku
170063	74 mangere rd otahuhu	74 Mangere Road, Otahuhu, Auckland
180116	1 otanerua rd hatfields beach	NaN
73814	531 whitmore rd matakana	NaN
167989	45 new windsor rd avondale	NaN
152383	9 clarke rd royal oak	NaN
150907	0 karioitahi rd waiuku	NaN
65901	2 o'rorke rd penrose	NaN
114640	130 bucklands beach rd bucklands beach	NaN
205755	10 watson pl	NaN
13441	303 flat bush school road flat bush auckland	NaN
93924	16 medvale ave otara	NaN
63351	149b sandhills rd great barrier island	NaN

addresses_no_na[addresses_no_na.full_address.str.contains('^[0-9]*\/303 Flat Bush', regex=True)]

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name

addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^300 Flat', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[13441]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^130 Bucklands Beach', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[114640]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^8 Clarke Road, Oneh', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[167989]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

# no exact 45 - needs to have a flat
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^[0-9]*\/45 New Windsor', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[167989]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

# no 531, even with a flat
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^51. Whitmore', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[73814]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

# way off
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^1 Otanerua', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[180116]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

def range_expand(r):
    if '/' in r:
        r, suffix = r.split('/')
        suffix = '/' + suffix
    else:
        suffix = ''
    r1, r2 = r.split('-')
    if r1.isdecimal() and r2.isdecimal():
        return [str(i) + suffix for i in range(int(r1), int(r2) + 1)]
    else:
        return r + suffix

ranged_list = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
    number_name = row.number_name.split(' ')
    number = number_name[0]
    name = number_name[1]
    range_expanded = range_expand(number)
    
    range_rows = [row.copy() for _ in range(len(range_expanded))]
    for i, rr in enumerate(range_rows):
        rr.number_name = range_expanded[i] + ' ' + name
    
    ranged_list.append(pd.DataFrame(range_rows))

# number of building consents that don't have NA for ADDRESS_1, and also don't have an address starting with a number
len([a for a in bcs[~bcs.ADDRESS_1.isna()].ADDRESS_1 if not a[0] in [str(i) for i in range(10)]])

3725

Join BCs with NZ Parcels

%%time
nz_parcels = gpd.read_file('input/lds-nz-primary-parcels-FGDB.zip!nz-primary-parcels.gdb')
print(nz_parcels.crs)
nz_parcels = nz_parcels.to_crs(3857)
nz_parcels = nz_parcels.cx[bounds_buffered3857[0]:bounds_buffered3857[2], bounds_buffered3857[1]:bounds_buffered3857[3]]

epsg:4167
CPU times: user 3min 58s, sys: 1.15 s, total: 3min 59s
Wall time: 3min 59s

nz_parcels = nz_parcels.cx[bounds_buffered3857[0]:bounds_buffered3857[2], bounds_buffered3857[1]:bounds_buffered3857[3]]

nz_parcels

	id	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	geometry
0	5266447	None	None	Hydro	Primary	None	North Auckland	None	NaN	1289690.0	MULTIPOLYGON (((-12155911.950 495830.979, -121...
1	4789727	Part Lot 3 Allot 64 Section 1 SBRS OF Auckland	SO 663	DCDB	Primary	None	North Auckland	None	NaN	1.0	MULTIPOLYGON (((-12296010.220 369113.384, -122...
2	4810316	Part Tidal Lands of Manukau Harbour Survey Off...	SO 67474	DCDB	Primary	[Create] Revested in the Crown Sec 5 Foreshore...	North Auckland	None	31600000.0	31342451.0	MULTIPOLYGON (((-12349176.218 373759.767, -123...
3	4817943	Crown Land Survey Office Plan 58175	SO 58175	DCDB	Primary	[Create] Crown Land Reserved from Sale Sec 58 ...	North Auckland	None	NaN	467490.0	MULTIPOLYGON (((-12183771.380 518898.007, -121...
4	4827816	Lot 2 DP 165098	DP 165098	DCDB	Primary	None	North Auckland	NA99B/977	22979.0	22972.0	MULTIPOLYGON (((-12254746.365 394243.168, -122...
5	4937273	Lot 2 DP 38555	DP 38555	DCDB	Primary	None	North Auckland	NA7A/846	726.0	727.0	MULTIPOLYGON (((-12311163.341 359403.649, -123...
6	4991598	Lot 161 DP 6975	DP 6975	DCDB	Primary	[Create] Historic Reserve [Kawau Island Histor...	North Auckland	None	1012.0	1045.0	MULTIPOLYGON (((-12174704.920 398868.292, -121...
7	5008028	Lot 3 DP 155195	DP 155195	DCDB	Primary	None	North Auckland	NA92C/966, NA94A/279	12.0	12.0	MULTIPOLYGON (((-12306276.415 367050.209, -123...
8	5014167	Crown Land	None	DCDB	Primary	[Create] Riverbank Reserve Sec 129 Land Act 1924	North Auckland	None	NaN	181271.0	MULTIPOLYGON (((-12225342.686 475656.177, -122...
9	5030697	Lot 4 DP 61316	DP 61316	DCDB	Primary	None	North Auckland	NA18B/1061	5.0	5.0	MULTIPOLYGON (((-12303511.996 347847.490, -123...
10	5266127	None	None	Hydro	Primary	None	North Auckland	None	NaN	258258.0	MULTIPOLYGON (((-12221698.709 476727.407, -122...
11	5043516	Part Tidal Lands of Manukau Harbour Survey Off...	SO 67474	DCDB	Primary	[Create] Revested in the Crown Sec 5 Foreshore...	North Auckland	None	802300.0	778257.0	MULTIPOLYGON (((-12342742.430 390472.136, -123...
12	5074709	Crown Land Survey Office Plan 44403	SO 44403	DCDB	Primary	[Create] Crown Land Reserved from Sale (Margin...	North Auckland	None	NaN	127235.0	MULTIPOLYGON (((-12199997.005 521092.551, -121...
13	5116430	Lot 2 DP 58587	DP 58587	DCDB	Primary	None	North Auckland	NA13B/1218	13.0	12.0	MULTIPOLYGON (((-12303179.527 373018.829, -123...
14	5122300	Lot 3 DP 22134	DP 22134	DCDB	Primary	None	North Auckland	NA610/117	3.0	2.0	MULTIPOLYGON (((-12296958.755 372226.919, -122...
...	...	...	...	...	...	...	...	...	...	...	...
537275	8022000	Lot 1 DP 522526	DP 522526	Fee Simple Title	Primary	None	North Auckland	830418	234875.0	234901.0	MULTIPOLYGON (((-12309085.450 290487.154, -123...
537276	7264068	Lot 615 DP 429024	DP 429024, LT 450627	Fee Simple Title	Primary	None	North Auckland	513806	447.0	447.0	MULTIPOLYGON (((-12336992.097 315398.428, -123...
537277	7264056	Lot 51 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513825, 513826, 513827, 513828, 513829, 513830...	1772.0	1771.0	MULTIPOLYGON (((-12337109.091 315547.825, -123...
537278	7190462	Lot 548 DP 420258	DP 420258	Fee Simple Title	Primary	None	North Auckland	483135	341.0	341.0	MULTIPOLYGON (((-12336188.725 314781.089, -123...
537279	7266272	Lot 536 DP 427884	DP 427884, LT 454543	Fee Simple Title	Primary	None	North Auckland	520878	338.0	338.0	MULTIPOLYGON (((-12336035.238 314657.366, -123...
537280	7264079	Lot 626 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513817	307.0	307.0	MULTIPOLYGON (((-12336879.505 315659.652, -123...
537281	5130443	Lot 30 DP 17583	DP 17583, DP 531059	DCDB	Primary	None	North Auckland	865806, NA78B/972	813.0	813.0	MULTIPOLYGON (((-12317776.688 369234.849, -123...
537282	7535538	Lot 88 DP 469841	DP 469841	Fee Simple Title	Primary	None	North Auckland	634066	389.0	389.0	MULTIPOLYGON (((-12336254.897 315319.544, -123...
537283	7264099	Lot 646 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513837	151.0	151.0	MULTIPOLYGON (((-12336903.659 315801.323, -123...
537284	8054279	Lot 24 DP 533358	DP 533358	Fee Simple Title	Primary	None	North Auckland	877006	229.0	229.0	MULTIPOLYGON (((-12288815.890 400558.457, -122...
537285	7291940	Lot 745 DP 433546	DP 433546	Fee Simple Title	Primary	None	North Auckland	528968	247.0	247.0	MULTIPOLYGON (((-12336398.380 315153.542, -123...
537286	7266269	Lot 533 DP 427884	DP 427884, LT 454543	Fee Simple Title	Primary	None	North Auckland	520875	336.0	336.0	MULTIPOLYGON (((-12335989.801 314781.682, -123...
537287	8051540	Lot 8 DP 533517	DP 533517	Fee Simple Title	Primary	None	North Auckland	876934	185.0	185.0	MULTIPOLYGON (((-12231484.279 417503.744, -122...
537288	7264065	Lot 518 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513803	313.0	313.0	MULTIPOLYGON (((-12336358.450 315254.637, -123...
537289	7266268	Lot 532 DP 427884	DP 427884, LT 454543	Fee Simple Title	Primary	None	North Auckland	520874	331.0	331.0	MULTIPOLYGON (((-12335919.436 314757.471, -123...

537290 rows × 11 columns

join with SA2s

sa2 = gpd.read_file('NZ-SA/statistical-area-2-2020-generalised.gdb')
sa2 = sa2.to_crs(3857)

gpd.sjoin(bc_sample, sa2)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	index_right	SA22020_V1_00	SA22020_V1_00_NAME	SA22020_V1_00_NAME_ASCII	LAND_AREA_SQ_KM	AREA_SQ_KM	SHAPE_Length
6440	219437	Aug 01, 2020 12:00:00 AM	Mar 01, 2020 12:00:00 AM	13 GRIFFEN PARK ROAD	Mount Roskill	Auckland	Puketapapa	Townhouses, flats, units, and other dwellings	1129	634.0	NaN	3	Residential	Attached	NaN	NaN	2014	587000.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.719937	-36.920856	POINT (19449734.429 -4428080.845)	1006	139600	Lynfield North	Lynfield North	0.940925	0.940925	5537.544865
203270	65577	Mar 01, 2000 12:00:00 AM	Apr 01, 2000 12:00:00 AM		DUNN RD	DRURY	Franklin	New (and pre-built) house, unit, bach, crib, b...	1100	207.0	95000.0	1	Residential	Detached	NaN	NaN	1998	815401.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	175.052449	-37.151529	POINT (19486749.463 -4460249.110)	1233	166400	Ararimu	Ararimu	90.925442	90.925442	58867.417446
66156	144992	May 01, 2013 12:00:00 AM	Mar 01, 2013 12:00:00 AM	39	BEACHWOOD DR	HATFIELDS BEACH	Hibiscus and Bays	New (and pre-built) house, unit, bach, crib, b...	1100	203.0	354000.0	1	Residential	Detached	NaN	NaN	1998	170807.0	2013.0	Residential - Single House Zone	Residential	19.0	RDC_27	Residential Medium Inten	Residential	NaN	NaN	NaN	174.686288	-36.567512	POINT (19445988.651 -4378993.739)	621	112800	Hatfields Beach	Hatfields Beach	0.923252	0.923252	4314.585524
91368	39665	Jan 01, 1997 12:00:00 AM	Apr 01, 1997 12:00:00 AM	45	PALOMINO DR	WESTERN HEIGHTS	Henderson - Massey	New (and pre-built) house, unit, bach, crib, b...	1100	210.0	100000.0	1	Residential	Detached	NaN	NaN	1998	281514.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.623138	-36.898135	POINT (19438958.803 -4424917.654)	908	128300	McLaren Park	McLaren Park	1.088384	1.088384	4957.562154
10567	211720	Mar 01, 2020 12:00:00 AM	Mar 01, 2020 12:00:00 AM	61 CLARK ROAD	Hobsonville	Auckland	Upper Harbour	Townhouses, flats, units, and other dwellings	1129	95.0	240000.0	1	Residential	Attached	NaN	NaN	2014	222800.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_16	Special Area	Special	NaN	Scott Point, Sunderland Precinct, Hobsonville ...	Scott Point, Sunderland Precinct, Hobsonville ...	174.654705	-36.799413	POINT (19442472.770 -4411184.328)	71	120200	Hobsonville Point	Hobsonville Point	3.521568	3.521568	11979.226387